﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace RegisterClientForm
{
    public class DataProvider
    {
        string connectionString = "Data Source=NHAT-PC;Initial Catalog=ClientDB;Integrated Security=True";

        public DataTable SetlectAllData()
        {
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select * from UserInfo", cnn);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            cmd.ExecuteNonQuery();
            cnn.Close();
            return dt;
        }

        public string insertData(ClassUser user)
        {
            string message;
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand("insert into UserInfo(Id, Name,Email,Flag) values(@Id,@Name,@Email,@Flag)", cnn);
            cmd.Parameters.AddWithValue("@Id", user.getId());
            cmd.Parameters.AddWithValue("@Name", user.getName());
            cmd.Parameters.AddWithValue("@Email", user.getEmail());
            cmd.Parameters.AddWithValue("@Flag", 0);

            int result = cmd.ExecuteNonQuery();
            if (result == 1)
                message = user.getName() + " insert successfully";
            else message = user.getName() + " not insert successfully";

            cnn.Close();
            return message;
        }

        public void deleteData(ClassUser user)
        {
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand("delete from UserInfo where Id = @Id", cnn);
            cmd.Parameters.AddWithValue("@Id", user.getId());
            cmd.ExecuteNonQuery();
            cnn.Close();
        }

        public void updateData(ClassUser user)
        {
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand("update UserInfo set Id=@Id, Name=@Name, Email=@Email, Flag=@Flag where Id=@ID", cnn);
            cmd.Parameters.AddWithValue("@Id", user.getId());
            cmd.Parameters.AddWithValue("@Name", user.getName());
            cmd.Parameters.AddWithValue("@Email", user.getEmail());
            cmd.Parameters.AddWithValue("@Flag", 0);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}
